'use strict';

const Service = require('egg').Service;
const Sequelize = require('sequelize');
const Op = Sequelize.Op;

class MusicService extends Service {
  // 歌曲列表
  async page(query) {
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        status:1
      },
      order:[
        ['playNum', 'DESC']
      ],
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM collection
                WHERE
                    collection.cid = music.id
                    AND
                    collection.type = 1
            )`),
            'collectionNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM likes
                WHERE
                    likes.lid = music.id
                    AND
                    likes.type = 1
            )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM comment
                WHERE
                    comment.cid = music.id
                    AND
                    comment.type = 1
                    AND
                    comment.isDel = 0
                    AND
                    comment.status = 1
            )`),
            'commentNum'
          ],
          [Sequelize.col('star.name'), 'starName'],
          [Sequelize.col('classify.name'), 'classifyName'],
          [Sequelize.col('album.name'), 'albumName']
        ],
        exclude: ['isDel','delTime','status','lyrics']
      },
      include: [
        {
          model: ctx.model.Star,
          as: 'star',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Classify,
          as: 'classify',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Album,
          as: 'album',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        }
      ],
      distinct: true
    };
    if(ctx.request.header.uid){
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM collection
          WHERE
            collection.cid = music.id
            AND
            collection.type = 1
            AND
            collection.uid = ${ctx.request.header.uid}
        )`),
        'isCollection'
      ]);
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM likes
          WHERE
              likes.lid = music.id
              AND
              likes.type = 1
              AND
              likes.uid = ${ctx.request.header.uid}
        )`),
        'isLikes'
      ]);
    }
    if(query.keyword){
      config.where.name = {
        [Op.like]:`%${query.keyword}%`
      }
    }
    if(query.sid){
      config.where.sid = query.sid;
    }
    if(query.cid){
      config.where.cid = query.cid;
    }
    if(query.aid){
      config.where.aid = query.aid;
    }
    if(!query.pageNO){
      query.pageNO = 1;
    }
    if(!query.pageSize){
      query.pageSize = 10;
    }
    config.offset = (query.pageNO-1)*query.pageSize;
    config.limit = query.pageSize;
    return await ctx.model.Music.findAndCountAll(config);
  }

  // 歌曲详情
  async detail(query) {
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        id:query.id,
        status:1
      },
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM collection
                WHERE
                    collection.cid = music.id
                    AND
                    collection.type = 1
            )`),
            'collectionNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM likes
                WHERE
                    likes.lid = music.id
                    AND
                    likes.type = 1
            )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM comment
                WHERE
                    comment.cid = music.id
                    AND
                    comment.type = 1
                    AND
                    comment.isDel = 0
                    AND
                    comment.status = 1
            )`),
            'commentNum'
          ],
          [Sequelize.col('star.name'), 'starName'],
          [Sequelize.col('classify.name'), 'classifyName'],
          [Sequelize.col('album.name'), 'albumName']
        ],
        exclude: ['isDel','delTime','status']
      },
      include: [
        {
          model: ctx.model.Star,
          as: 'star',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Classify,
          as: 'classify',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Album,
          as: 'album',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        }
      ]
    };
    if(ctx.request.header.uid){
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM collection
          WHERE
            collection.cid = music.id
            AND
            collection.type = 1
            AND
            collection.uid = ${ctx.request.header.uid}
        )`),
        'isCollection'
      ]);
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM likes
          WHERE
              likes.lid = music.id
              AND
              likes.type = 1
              AND
              likes.uid = ${ctx.request.header.uid}
        )`),
        'isLikes'
      ]);
    }
    return await ctx.model.Music.findOne(config);
  }

  // 歌曲播放量加1
  async addPlayNum(data) {
    const { ctx } = this;
    const [results, metadata] = await ctx.model.query(
      'UPDATE music SET playNum=playNum+1 WHERE id = ?',{
      replacements: [data.id]
    });
    return results;
  }

  // 歌曲评论列表
  async commentPage(query) {
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        cid:query.cid,
        type:1,
        status:1
      },
      order:[
        ['createTime', 'DESC']
      ],
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM likes
                  WHERE
                      likes.lid = comment.id
                      AND
                      likes.type = 5
              )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM comment AS c
                  WHERE
                      c.pid = comment.id
                      AND
                      c.type = 1
                      AND
                      c.isDel = 0
                      AND
                      c.status = 1
              )`),
            'commentNum'
          ],
          [Sequelize.col('user.nickname'), 'nickname']
        ],
        exclude: ['isDel','delTime','type','status','uid'],
      },
      include: [{
        model: ctx.model.User,
        as: 'user',
        duplicating:false,
        required:false,
        attributes: []
      }],
      distinct: true
    };
    if(ctx.request.header.uid){
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM likes
          WHERE
              likes.lid = comment.id
              AND
              likes.type = 5
              AND
              likes.uid = ${ctx.request.header.uid}
        )`),
        'isLikes'
      ]);
    }
    config.where.pid = query.pid?query.pid:0;
    if(!query.pageNO){
      query.pageNO = 1;
    }
    if(!query.pageSize){
      query.pageSize = 10;
    }
    config.offset = (query.pageNO-1)*query.pageSize;
    config.limit = query.pageSize;
    return await ctx.model.Comment.findAndCountAll(config);
  }

  // 歌曲评论/回复
  async comment(data) {
    const { ctx } = this;
    return await ctx.model.Comment.create({
      cid:data.cid,
      pid:data.pid?data.pid:0,
      content:data.content,
      type:1,
      uid:ctx.request.header.uid,
      createTime:new Date().getTime(),
      updateTime:new Date().getTime(),
      delTime:0
    });
  }

  // 歌曲评论是否已点赞
  async isCommentLikes(data){
    const { ctx } = this;
    let config = {
      where:{
        lid:data.lid,
        type:5,
        uid:ctx.request.header.uid
      },
      attributes: ['id']
    };
    return await ctx.model.Likes.findOne(config);
  }

  // 歌曲评论点赞
  async doCommentLikes(data) {
    const { ctx } = this;
    return await ctx.model.Likes.create({
      lid:data.lid,
      type:5,
      uid:ctx.request.header.uid,
      createTime:new Date().getTime()
    });
  }

  // 歌曲评论取消点赞
  async doClearCommentLikes(data) {
    const { ctx } = this;
    return await ctx.model.Likes.destroy({
      where:{
        lid:data.lid,
        type:5,
        uid:ctx.request.header.uid
      }
    });
  }

  // 歌曲是否已点赞
  async isLikes(data){
    const { ctx } = this;
    let config = {
      where:{
        lid:data.lid,
        type:1,
        uid:ctx.request.header.uid
      },
      attributes: ['id']
    };
    return await ctx.model.Likes.findOne(config);
  }

  // 歌曲点赞
  async doLikes(data) {
    const { ctx } = this;
    return await ctx.model.Likes.create({
      lid:data.lid,
      type:1,
      uid:ctx.request.header.uid,
      createTime:new Date().getTime()
    });
  }

  // 歌曲取消点赞
  async doClearLikes(data) {
    const { ctx } = this;
    return await ctx.model.Likes.destroy({
      where:{
        lid:data.lid,
        type:1,
        uid:ctx.request.header.uid
      }
    });
  }

  // 歌曲是否已收藏
  async isCollection(data){
    const { ctx } = this;
    let config = {
      where:{
        cid:data.cid,
        type:1,
        uid:ctx.request.header.uid
      },
      attributes: ['id']
    };
    return await ctx.model.Collection.findOne(config);
  }

  // 歌曲收藏
  async doCollection(data) {
    const { ctx } = this;
    return await ctx.model.Collection.create({
      cid:data.cid,
      type:1,
      uid:ctx.request.header.uid,
      createTime:new Date().getTime()
    });
  }

  // 歌曲取消收藏
  async doClearCollection(data) {
    const { ctx } = this;
    return await ctx.model.Collection.destroy({
      where:{
        cid:data.cid,
        type:1,
        uid:ctx.request.header.uid
      }
    });
  }

  // 收藏的歌曲
  async collectionPage(query) {
    const { ctx } = this;
    let collectionResult = await ctx.model.Collection.findAll({
      where:{
        type:1,
        uid:ctx.request.header.uid
      },
      attributes: ['cid']
    });
    let collectionList = [];
    for(let i of collectionResult){
      collectionList.push(i.cid);
    }
    let config = {
      where:{
        isDel:0,
        status:1,
        id: {
          [Op.in]:collectionList
        }
      },
      order:[
        ['playNum', 'DESC']
      ],
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM collection
                WHERE
                    collection.cid = music.id
                    AND
                    collection.type = 1
            )`),
            'collectionNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM likes
                WHERE
                    likes.lid = music.id
                    AND
                    likes.type = 1
            )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                SELECT COUNT(*)
                FROM comment
                WHERE
                    comment.cid = music.id
                    AND
                    comment.type = 1
                    AND
                    comment.isDel = 0
                    AND
                    comment.status = 1
            )`),
            'commentNum'
          ],
          [
            Sequelize.literal(`(
              SELECT COUNT(*)
              FROM collection
              WHERE
                collection.cid = music.id
                AND
                collection.type = 1
                AND
                collection.uid = ${ctx.request.header.uid}
            )`),
            'isCollection'
          ],
          [
            Sequelize.literal(`(
              SELECT COUNT(*)
              FROM likes
              WHERE
                  likes.lid = music.id
                  AND
                  likes.type = 1
                  AND
                  likes.uid = ${ctx.request.header.uid}
            )`),
            'isLikes'
          ],
          [Sequelize.col('star.name'), 'starName'],
          [Sequelize.col('classify.name'), 'classifyName'],
          [Sequelize.col('album.name'), 'albumName']
        ],
        exclude: ['isDel','delTime','status','lyrics']
      },
      include: [
        {
          model: ctx.model.Star,
          as: 'star',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Classify,
          as: 'classify',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Album,
          as: 'album',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        }
      ],
      distinct: true
    };
    if(!query.pageNO){
      query.pageNO = 1;
    }
    if(!query.pageSize){
      query.pageSize = 10;
    }
    config.offset = (query.pageNO-1)*query.pageSize;
    config.limit = query.pageSize;
    return await ctx.model.Music.findAndCountAll(config);
  }

  // 播放量排名前十歌曲
  async getTopMusic(){
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        status:1
      },
      offset:0,
      limit:10,
      order:[
        ['playNum', 'DESC']
      ],
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM collection
                  WHERE
                      collection.cid = music.id
                      AND
                      collection.type = 1
              )`),
            'collectionNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM likes
                  WHERE
                      likes.lid = music.id
                      AND
                      likes.type = 1
              )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM comment
                  WHERE
                      comment.cid = music.id
                      AND
                      comment.type = 1
                      AND
                      comment.isDel = 0
                      AND
                      comment.status = 1
              )`),
            'commentNum'
          ],
          [Sequelize.col('star.name'), 'starName'],
          [Sequelize.col('classify.name'), 'classifyName'],
          [Sequelize.col('album.name'), 'albumName']
        ],
        exclude: ['isDel','delTime','lyrics'],
      },
      include: [
        {
          model: ctx.model.Star,
          as: 'star',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Classify,
          as: 'classify',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Album,
          as: 'album',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        }
      ]
    };
    if(ctx.request.header.uid){
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM collection
          WHERE
            collection.cid = music.id
            AND
            collection.type = 1
            AND
            collection.uid = ${ctx.request.header.uid}
        )`),
        'isCollection'
      ]);
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM likes
          WHERE
              likes.lid = music.id
              AND
              likes.type = 1
              AND
              likes.uid = ${ctx.request.header.uid}
        )`),
        'isLikes'
      ]);
    }
    return await ctx.model.Music.findAll(config);
  }

  // 全文搜索
  async search(query){
    const { ctx } = this;
    let config = {
      where:{
        isDel:0,
        status:1,
        name:{
          [Op.like]:`%${query.keyword}%`
        }
      },
      offset:0,
      limit:5,
      order:[
        ['playNum', 'DESC']
      ],
      attributes: {
        include: [
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM collection
                  WHERE
                      collection.cid = music.id
                      AND
                      collection.type = 1
              )`),
            'collectionNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM likes
                  WHERE
                      likes.lid = music.id
                      AND
                      likes.type = 1
              )`),
            'likesNum'
          ],
          [
            Sequelize.literal(`(
                  SELECT COUNT(*)
                  FROM comment
                  WHERE
                      comment.cid = music.id
                      AND
                      comment.type = 1
                      AND
                      comment.isDel = 0
                      AND
                      comment.status = 1
              )`),
            'commentNum'
          ],
          [Sequelize.col('star.name'), 'starName'],
          [Sequelize.col('classify.name'), 'classifyName'],
          [Sequelize.col('album.name'), 'albumName']
        ],
        exclude: ['isDel','delTime','lyrics'],
      },
      include: [
        {
          model: ctx.model.Star,
          as: 'star',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Classify,
          as: 'classify',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        },
        {
          model: ctx.model.Album,
          as: 'album',
          where: {
            isDel:0
          },
          duplicating:false,
          required:false,
          attributes: []
        }
      ]
    };
    if(ctx.request.header.uid){
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM collection
          WHERE
            collection.cid = music.id
            AND
            collection.type = 1
            AND
            collection.uid = ${ctx.request.header.uid}
        )`),
        'isCollection'
      ]);
      config.attributes.include.push([
        Sequelize.literal(`(
          SELECT COUNT(*)
          FROM likes
          WHERE
              likes.lid = music.id
              AND
              likes.type = 1
              AND
              likes.uid = ${ctx.request.header.uid}
        )`),
        'isLikes'
      ]);
    }
    return await ctx.model.Music.findAll(config);
  }
}
module.exports = MusicService;
